<?php
/**
 * SpecialsController class.
 * SpecialsController is for the list of specials.
 * 
 * LBB Solutions Ltd. 版权所有 @ 2012.
 */
class SpecialsController extends ApiController
{
	/**
	 * API: specials/index
	 */
	public function actionIndex()
	{
		// set the limit number and offset
		$offset = Yii::app()->request->getParam('offset', 0);
		$limit = Yii::app()->request->getParam('limit', 100);
		if ($limit > 100) $limit = 100;
		$s_limit = " LIMIT {$offset}, " . $limit;
		
		$params = array();
		
		// get distance if lat=? and lng=?
		$enable_geo = false;
		$s_distance = 'NULL AS distance, ';
		$latitude = Yii::app()->request->getParam('lat');
		$longitude= Yii::app()->request->getParam('lng');
		if (!empty($latitude) and !empty($longitude)) {
			$enable_geo = true;
			$s_distance = '(6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance, ';
			$params[':lat'] = (double)$latitude;
			$params[':lng'] = (double)$longitude;
		}
		
		// get sort:
		$s_orderby = '';
		$this->_setOrderBy($s_orderby);
		
		// get filter:
		$s_where = '';
		$this->_setFilters($s_where, $params);
		
		$s_status = null;
		// get hid (user id)
		$id = null;
		$hid = Yii::app()->request->getParam('hid');
		$key = Yii::app()->request->getParam('key');
		if (!empty($hid) and !empty($key)) {
			$id = $this->_auth($hid, $key);
		}
		// get device_token: token=?
		$device_token = Yii::app()->request->getParam('token');
		if (!empty($device_token) or !empty($id)) {
			$s_status = ', (SELECT r3.redeemed FROM tbl_merchant_special_redeem r3
					WHERE r3.id_special=s.id AND (r3.device_token=:device_token OR r3.id_user=:id_user) AND DATE(r3.insert_timestamp)=DATE(NOW())
					ORDER BY r3.insert_timestamp DESC LIMIT 1) AS redeemed_status
					, (SELECT unix_timestamp(r4.redeemed_time) FROM tbl_merchant_special_redeem r4
					 WHERE r4.id_special=s.id AND (r4.device_token=:device_token OR r4.id_user=:id_user) AND DATE(r4.insert_timestamp)=DATE(NOW())
					 ORDER BY r4.insert_timestamp DESC LIMIT 1) AS redeemed_time
					, (SELECT COUNT(f.id) FROM tbl_user_follow_merchant f
					   WHERE f.id_user=:id_user AND f.id_merchant=m.id) AS following ';// add the following data
			$params[':id_user'] = $id;
			$params[':device_token'] = $device_token;
		}
		
		// fetch specials
		// comments: only care today's claimed number: DATE(r1.insert_timestamp)=DATE(NOW())
		$params[':week_days'] = '%'.date('N').'%';	//week_days => 5
		$params[':hour_min'] = (int)date('Gi');		//hour_min => 934
		$sql_specials = 'SELECT s.id, s.id_merchant, s.url_picture, s.content, s.show_hours, s.start_hour, s.end_hour, s.week_days,
			s.price, s.value, s.discount, s.highlight, s.type, ' . $s_distance . '
			UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date, UNIX_TIMESTAMP(s.published_time) AS uts_published_time,
			s.max_number, s.redeem_type,
			m.name, m.address, m.biz_area, m.postcode, m.city, m.phone, m.url_logo, m.url_offer_img, m.currency_code, m.category_key, m.latitude, m.longitude, '
			. ' (SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1
				 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num, '	// calculate today's claimed_num
			. ' (SELECT SUM(r2.number) FROM tbl_merchant_special_redeem r2
				 WHERE r2.id_special=s.id AND r2.redeemed=2 AND DATE(r2.redeemed_time)=DATE(NOW()) ) AS redeemed_num '	// calculate today's redeemed_num
			. $s_status . ' 
			FROM tbl_merchant_special s
			INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant
			WHERE s.status=1 AND s.trash=0 AND m.status<2 AND s.start_date<NOW() AND s.end_date>NOW() 
			    AND s.price IS NOT NULL AND s.value IS NOT NULL '//TODO: work for old versions
			. ' AND (s.week_days LIKE :week_days 
			       AND ( 
				        (s.start_hour<s.end_hour AND s.start_hour<=:hour_min AND s.end_hour>=:hour_min)
					 OR (s.start_hour>s.end_hour AND s.start_hour<=:hour_min AND s.end_hour+2400>=:hour_min) 
					 OR (s.start_hour>s.end_hour AND s.start_hour>:hour_min AND s.end_hour>=:hour_min) 
					 )
				  )
			' . $s_where . '
			ORDER BY ' . $s_orderby
			. $s_limit;
		//print_r(array($sql_specials, $params));
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_specials);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		$this->_sendJSON(200, $this->_mkSpecials($rows));
	}
	
	/**
	 * API: specials/next24
	 * 查询未来24小时内优惠活动
	 */
	public function actionNext24()
	{
		// set the limit number and offset
		$offset = Yii::app()->request->getParam('offset', 0);
		$limit = Yii::app()->request->getParam('limit', 20);
		if ($limit > 50) $limit = 50;
		$s_limit = " LIMIT {$offset}, " . $limit;
		
		$params = array();
		
		// get distance if lat=? and lng=?
		$enable_geo = false;
		$s_distance = 'NULL AS distance, ';
		$latitude = Yii::app()->request->getParam('lat');
		$longitude= Yii::app()->request->getParam('lng');
		if (!empty($latitude) and !empty($longitude)) {
			$enable_geo = true;
			$s_distance = '(6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance, ';
			$params[':lat'] = (double)$latitude;
			$params[':lng'] = (double)$longitude;
		}
		
		// get sort:
		$s_orderby = '';
		$this->_setOrderBy($s_orderby);
		
		// get filter:
		$s_where = '';
		$this->_setFilters($s_where, $params);
		
		$s_status = null;
		// get hid (user id)
		$id = null;
		$hid = Yii::app()->request->getParam('hid');
		$key = Yii::app()->request->getParam('key');
		if (!empty($hid) and !empty($key)) {
			$id = $this->_auth($hid, $key);
			$s_status = ', (SELECT COUNT(f.id) FROM tbl_user_follow_merchant f
					   WHERE f.id_user=:id_user AND f.id_merchant=m.id) AS following ';// add the following data
			$params[':id_user'] = $id;
		}
		
		// fetch specials
		$today_weekno = date('N');
		$tomorrow_weekno = $today_weekno + 1; if ($today_weekno == 7) $tomorrow_weekno = 1;
		$params[':today_week'] = '%'.$today_weekno.'%';
		$params[':tomorrow_week'] = '%'.$tomorrow_weekno.'%';
		$params[':hour_min'] = (int)date('Gi');		//hour_min => 934
		$params[':tomorrow'] = date('Y-m-d', mktime(0, 0, 0, date('m'), date('d')+1));
		$sql_specials = 'SELECT s.id, s.id_merchant, s.url_picture, s.content, s.show_hours, s.start_hour, s.end_hour, s.week_days,
			s.price, s.value, s.discount, s.highlight, s.type, ' . $s_distance . '
			UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date, UNIX_TIMESTAMP(s.published_time) AS uts_published_time,
			s.max_number, s.redeem_type,
			m.name, m.address, m.biz_area, m.postcode, m.city, m.phone, m.url_logo, m.url_offer_img, m.currency_code, m.category_key, m.latitude, m.longitude '
			. ', 0 AS claimed_num '  // nobody can claim
			. ', 0 AS redeemed_num ' // nobody can redeem
			. ', 0 AS redeemed_status ' // status is always 0
			. $s_status
			. ' FROM tbl_merchant_special s
			INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant
			WHERE s.status=1 AND s.trash=0 AND m.status<2 AND s.end_date>NOW() AND DATE(s.start_date)<=:tomorrow
			  AND (s.week_days LIKE :tomorrow_week OR (s.week_days LIKE :today_week AND s.start_hour>:hour_min))
			' . $s_where . '
			ORDER BY ' . $s_orderby
			. $s_limit;
		//print_r(array($sql_specials, $params));
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_specials);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		$this->_sendJSON(200, $this->_mkSpecials($rows));
	}

	/**
	 * API: specials/search (for version 1.1.3)
	 */
	public function actionSearch()
	{
		// set the limit number and offset
		$offset = Yii::app()->request->getParam('offset', 0);
		$limit = Yii::app()->request->getParam('limit', 20);
		if ($limit > 50) $limit = 50;
		$s_limit = " LIMIT {$offset}, " . $limit;
		
		$params = array();
		
		// get distance if lat=? and lng=?
		$enable_geo = false;
		$s_distance = 'NULL AS distance, ';
		$latitude = Yii::app()->request->getParam('lat');
		$longitude= Yii::app()->request->getParam('lng');
		if (!empty($latitude) and !empty($longitude)) {
			$enable_geo = true;
			$s_distance = '(6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance, ';
			$params[':lat'] = (double)$latitude;
			$params[':lng'] = (double)$longitude;
		}
		
		// get sort:
		$s_orderby = '';
		$this->_setOrderBy($s_orderby);
		
		// get filter:
		$s_where = '';
		$this->_setFilters($s_where, $params);
		
		$s_status = null;
		// get hid (user id)
		$id = null;
		$hid = Yii::app()->request->getParam('hid');
		$key = Yii::app()->request->getParam('key');
		if (!empty($hid) and !empty($key)) {
			$id = $this->_auth($hid, $key);
		}
		// get device_token: token=?
		$device_token = Yii::app()->request->getParam('token');
		if (!empty($device_token) or !empty($id)) {
			$s_status = ', (SELECT r3.redeemed FROM tbl_merchant_special_redeem r3
					WHERE r3.id_special=s.id AND (r3.device_token=:device_token OR r3.id_user=:id_user) AND DATE(r3.insert_timestamp)=DATE(NOW())
					ORDER BY r3.insert_timestamp DESC LIMIT 1) AS redeemed_status
					, (SELECT unix_timestamp(r4.redeemed_time) FROM tbl_merchant_special_redeem r4
					 WHERE r4.id_special=s.id AND (r4.device_token=:device_token OR r4.id_user=:id_user) AND DATE(r4.insert_timestamp)=DATE(NOW())
					 ORDER BY r4.insert_timestamp DESC LIMIT 1) AS redeemed_time
					, (SELECT COUNT(f.id) FROM tbl_user_follow_merchant f
					   WHERE f.id_user=:id_user AND f.id_merchant=m.id) AS following ';// add the following data
			$params[':id_user'] = $id;
			$params[':device_token'] = $device_token;
		}
		
		// fetch specials
		// comments: only care today's claimed number: DATE(r1.insert_timestamp)=DATE(NOW())
		$today_weekno = date('N');
		$tomorrow_weekno = $today_weekno + 1; if ($today_weekno == 7) $tomorrow_weekno = 1;
		$params[':today_week'] = '%'.$today_weekno.'%';
		$params[':tomorrow_week'] = '%'.$tomorrow_weekno.'%';
		$sql_specials = 'SELECT s.id, s.id_merchant, s.url_picture, s.content, s.show_hours, s.start_hour, s.end_hour, s.week_days,
			s.price, s.value, s.discount, s.highlight, s.type, ' . $s_distance . '
			UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date, UNIX_TIMESTAMP(s.published_time) AS uts_published_time,
			s.max_number, s.redeem_type,
			m.name, m.address, m.biz_area, m.postcode, m.city, m.phone, m.url_logo, m.url_offer_img, m.currency_code, m.category_key, m.latitude, m.longitude, '
			. ' (SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1
				 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num, '	// calculate today's claimed_num
			. ' (SELECT SUM(r2.number) FROM tbl_merchant_special_redeem r2
				 WHERE r2.id_special=s.id AND r2.redeemed=2 AND DATE(r2.redeemed_time)=DATE(NOW()) ) AS redeemed_num '	// calculate today's redeemed_num
			. $s_status . ' 
			FROM tbl_merchant_special s
			INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant
			WHERE s.status=1 AND s.trash=0 AND m.status<2 AND s.start_date<NOW() AND s.end_date>NOW()
			  AND (s.week_days LIKE :today_week OR s.week_days LIKE :tomorrow_week)
			' . $s_where . '
			GROUP BY s.id
			ORDER BY ' . $s_orderby
			. $s_limit;
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_specials);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		$this->_sendJSON(200, $this->_mkSpecials($rows));
	}
	
	/**
	 * API: specials/search24 (for version 1.1.3)
	 * 查询未来24小时内优惠活动
	 */
	public function actionSearch24()
	{
		// set the limit number and offset
		$offset = Yii::app()->request->getParam('offset', 0);
		$limit = Yii::app()->request->getParam('limit', 20);
		if ($limit > 50) $limit = 50;
		$s_limit = " LIMIT {$offset}, " . $limit;
		
		$params = array();
		
		// get distance if lat=? and lng=?
		$enable_geo = false;
		$s_distance = 'NULL AS distance, ';
		$latitude = Yii::app()->request->getParam('lat');
		$longitude= Yii::app()->request->getParam('lng');
		if (!empty($latitude) and !empty($longitude)) {
			$enable_geo = true;
			$s_distance = '(6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance, ';
			$params[':lat'] = (double)$latitude;
			$params[':lng'] = (double)$longitude;
		}
		
		// get sort:
		$s_orderby = '';
		$this->_setOrderBy($s_orderby);
		
		// get filter:
		$s_where = '';
		$this->_setFilters($s_where, $params);
		
		$s_status = null;
		// get hid (user id)
		$id = null;
		$hid = Yii::app()->request->getParam('hid');
		$key = Yii::app()->request->getParam('key');
		if (!empty($hid) and !empty($key)) {
			$id = $this->_auth($hid, $key);
			$s_status = ', (SELECT COUNT(f.id) FROM tbl_user_follow_merchant f
					   WHERE f.id_user=:id_user AND f.id_merchant=m.id) AS following ';// add the following data
			$params[':id_user'] = $id;
		}
		
		// fetch specials
		$today_weekno = date('N');
		$tomorrow_weekno = $today_weekno + 1; if ($today_weekno == 7) $tomorrow_weekno = 1;
		$params[':today_week'] = '%'.$today_weekno.'%';
		$params[':tomorrow_week'] = '%'.$tomorrow_weekno.'%';
		$params[':hour_min'] = (int)date('Gi');		//hour_min => 934
		$params[':tomorrow'] = date('Y-m-d', mktime(0, 0, 0, date('m'), date('d')+1));
		$sql_specials = 'SELECT s.id, s.id_merchant, s.url_picture, s.content, s.show_hours, s.start_hour, s.end_hour, s.week_days,
			s.price, s.value, s.discount, s.highlight, s.type, ' . $s_distance . '
			UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date, UNIX_TIMESTAMP(s.published_time) AS uts_published_time,
			s.max_number, s.redeem_type,
			m.name, m.address, m.biz_area, m.postcode, m.city, m.phone, m.url_logo, m.url_offer_img, m.currency_code, m.category_key, m.latitude, m.longitude '
			. ', 0 AS claimed_num '  // nobody can claim
			. ', 0 AS redeemed_num ' // nobody can redeem
			. ', 0 AS redeemed_status ' // status is always 0
			. $s_status
			. ' FROM tbl_merchant_special s
			INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant
			WHERE s.status=1 AND s.trash=0 AND m.status<2 AND s.end_date>NOW() AND DATE(s.start_date)<=:tomorrow
			  AND (s.week_days LIKE :tomorrow_week OR (s.week_days LIKE :today_week AND s.start_hour>:hour_min))
			' . $s_where . '
			ORDER BY ' . $s_orderby
			. $s_limit;
		//print_r(array($sql_specials, $params));
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_specials);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		$this->_sendJSON(200, $this->_mkSpecials($rows));
	}
	
	/**
	 * API: specials/merchant
	 */
	public function actionMerchant()
	{
		// set the limit number and offset
		$offset = Yii::app()->request->getParam('offset', 0);
		$limit = Yii::app()->request->getParam('limit', 100);
		if ($limit > 100) $limit = 100;
		$s_limit = " LIMIT {$offset}, " . $limit;
		
		$params = array();
		
		// get filter: mid=?
		$s_where = '';
		$mid = Yii::app()->request->getParam('mid');
		if ($mid > 0) {
			$s_where = ' AND s.id_merchant=:id_merchant ';
			$params[':id_merchant'] = $mid;
		} else {
			$this->_sendError('The parameter "mid" is missing or invalid!');
		}
		
		// get distance if lat=? and lng=?
		$enable_geo = false;
		$s_distance = 'NULL AS distance, ';
		$latitude = Yii::app()->request->getParam('lat');
		$longitude= Yii::app()->request->getParam('lng');
		if (!empty($latitude) and !empty($longitude)) {
			$enable_geo = true;
			$s_distance = '(6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance, ';
			$params[':lat'] = (double)$latitude;
			$params[':lng'] = (double)$longitude;
		}
		
		$s_status = null;
		// get hid (user id)
		$id = null;
		$hid = Yii::app()->request->getParam('hid');
		$key = Yii::app()->request->getParam('key');
		if (!empty($hid) and !empty($key)) {
			$id = $this->_auth($hid, $key);
		}
		// get device_token: token=?
		$device_token = Yii::app()->request->getParam('token');
		if (!empty($device_token) or !empty($id)) {
			$s_status = ', (SELECT r3.redeemed FROM tbl_merchant_special_redeem r3
					WHERE r3.id_special=s.id AND (r3.device_token=:device_token OR r3.id_user=:id_user) AND DATE(r3.insert_timestamp)=DATE(NOW())
					ORDER BY r3.insert_timestamp DESC LIMIT 1) AS redeemed_status
					, (SELECT unix_timestamp(r4.redeemed_time) FROM tbl_merchant_special_redeem r4
					 WHERE r4.id_special=s.id AND (r4.device_token=:device_token OR r4.id_user=:id_user) AND DATE(r4.insert_timestamp)=DATE(NOW())
					 ORDER BY r4.insert_timestamp DESC LIMIT 1) AS redeemed_time
					, (SELECT COUNT(f.id) FROM tbl_user_follow_merchant f
					   WHERE f.id_user=:id_user AND f.id_merchant=m.id) AS following ';// add the following data
			$params[':id_user'] = $id;
			$params[':device_token'] = $device_token;
		}
		
		// fetch specials
		// comments: only care today's claimed number: DATE(r1.insert_timestamp)=DATE(NOW())
		$sql_specials = 'SELECT s.id, s.id_merchant, s.url_picture, s.content, s.show_hours, s.start_hour, s.end_hour, s.week_days,
			s.price, s.value, s.discount, s.highlight, s.type, ' . $s_distance . '
			UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date, UNIX_TIMESTAMP(s.published_time) AS uts_published_time,
			s.max_number, s.redeem_type,
			m.name, m.address, m.biz_area, m.postcode, m.city, m.phone, m.url_logo, m.url_offer_img, m.currency_code, m.category_key, m.latitude, m.longitude, '
			. ' (SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1
				 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num, '	// calculate today's claimed_num
			. ' (SELECT SUM(r2.number) FROM tbl_merchant_special_redeem r2
				 WHERE r2.id_special=s.id AND r2.redeemed=2 AND DATE(r2.redeemed_time)=DATE(NOW()) ) AS redeemed_num '	// calculate today's redeemed_num
			. $s_status . ' 
			FROM tbl_merchant_special s
			INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant
			WHERE s.status=1 AND s.trash=0 AND s.end_date>NOW()
			' . $s_where . '
			ORDER BY s.published_time DESC '
			. $s_limit;
		//print_r(array($sql_specials, $params));
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_specials);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		$this->_sendJSON(200, $this->_mkSpecials($rows));
	}
	
	/**
	 * API: specials/notify
	 * The new API will check the location bwteen 10 mins, if < 1km, then push a notification.
	 */
	public function actionNotify()
	{
		// get distance if lat=? and lng=?
		$latitude = Yii::app()->request->getParam('lat');
		$longitude= Yii::app()->request->getParam('lng');
		if (empty($latitude) or empty($longitude)) {
			$this->_sendError('The parameter "lat" or "lng" is missing!');
		}
		
		// get device token
		$device_token = Yii::app()->request->getParam('token');
		if (empty($latitude) or empty($longitude)) {
			$this->_sendError('The parameter "token" is missing!');
		}
		
		$current_hour = (int)date('G');
		if ($current_hour > 9 and $current_hour < 21) {
			//only push notification between 10:00 - 20:00
			$request_url = Yii::app()->createAbsoluteUrl($this->module->id . '/specials/pushOffer');
			BUtils::curl_post_async($request_url, array(
				'lat' => $latitude,
				'lng' => $longitude,
				'token' => $device_token
			));
		}
		
		// return null array
		$this->_sendJSON(200, $this->_mkSpecials(array()));
	}
	
	public function actionPushOffer1() {
		//php -q /home/yamiifi/public_html/index-cron.php Trigger pushOffer10Mins debug 9774d56d682e549c 60.2220279 24.8195250
		exec('php /home/yamiifi/public_html/index-cron.php Trigger debug pushOffer10Mins 9774d56d682e549c 60.2220279 24.8195250');
	}
	
	public function actionPushOffer() {
		// get distance if lat=? and lng=?
		$latitude = Yii::app()->request->getParam('lat');
		$longitude= Yii::app()->request->getParam('lng');
		if (empty($latitude) or empty($longitude)) {
			$this->_sendError('The parameter "lat" or "lng" is missing!');
		}
		
		// get device token
		$device_token = Yii::app()->request->getParam('token');
		if (empty($device_token)) {
			$this->_sendError('The parameter "token" is missing!');
		}
		
		self::APILogging('actionPushOffer', array(
			'microtime' => microtime(true),
			'token' => $device_token,
			'location' => "[{$latitude}, {$longitude}]",
		), 'specials_pushOffer');
		
		set_time_limit(0);
		ini_set('max_input_time', '-1');
		ini_set('max_execution_time', '0');
		sleep(600);//sleep 10 mins
		
		//get the latest lat,lng
		$sql_settings = 'SELECT UNIX_TIMESTAMP(update_time) as ts_updated, latitude, longitude, max_distance, min_discount,
			lang, device_type, device_notification_token, id_user
			FROM tbl_user_device_settings
			WHERE device_token=:device_token AND device_notification_token IS NOT NULL ';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_settings);
		$v = $command->queryRow(true, array(':device_token' => $device_token));
		if (empty($v)) {
			$connection->active = false;
			$this->_sendJSON(200, 'OK');
		}
		
		//1. check the speed (1.2m/s == 720m/10mins) for push notification
		$moving_distance = BUtils::get_distance($latitude, $longitude, $v['latitude'], $v['longitude']);
		if ($moving_distance < 0.72) {
			//2. start pushing notification with offers nearby
			//Only send the offer which hasn't been pushed to the current device
			//it's related to the logs: if it's already pushed, then push_times will larger than 0.
			//1) the latest offer
			$sql_offer = 'SELECT s.id, s.content, s.price, s.value, s.discount, m.name, m.latitude, m.longitude,
			(6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance
			FROM tbl_merchant_special s
			INNER JOIN tbl_merchant_account m ON m.id = s.id_merchant
			WHERE s.type IN (1,2) AND s.status=1 AND s.trash=0 AND s.start_date<NOW() AND s.end_date>NOW()
			  AND s.week_days LIKE :week_days 
			  AND ( 
				        (s.start_hour<s.end_hour AND s.start_hour<=:hour_min AND s.end_hour>=:hour_min)
					 OR (s.start_hour>s.end_hour AND s.start_hour<=:hour_min) 
					 OR (s.start_hour>s.end_hour AND s.start_hour>:hour_min AND s.end_hour>=:hour_min) 
			  )
			  AND s.discount>:discount
			  AND (6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) < :distance
			  AND (SELECT COUNT(*) FROM tbl_user_device_pushing_log WHERE device_token=:device_token AND id_special=s.id) < 1
			ORDER BY s.published_time DESC
			LIMIT 1';
			$params = array(
				':lat' => $latitude,
				':lng' => $longitude,
				':week_days' => '%'.date('N').'%',
				':hour_min' => (int)date('Gi'),
				':device_token' => $device_token,
				':discount' => $v['min_discount'],
				':distance' => $v['max_distance'],
			);
			
			
			$command = $connection->createCommand($sql_offer);
			$row = $command->queryRow(true, $params);
			//the validate in mysql first
			if (!empty($row['id'])) {
				if (empty($v['device_notification_token'])) {
					$connection->active = false;
					Yii::app()->end();
				}
				
				//make message
				$tmpl['fi'] = 'Uusi tarjous löytyy: {merchant_name}.';
				$tmpl['en'] = 'New offers found at {merchant_name}.';
				//check the langauge per device
				switch (strtolower($v['lang'])) {
					case 'fi':
					case 'fi_fi':
						$message = str_replace('{merchant_name}', $row['name'], $tmpl['fi']);
						$lang = 'fi';
						break;
					default:
						$message = str_replace('{merchant_name}', $row['name'], $tmpl['en']);
						$lang = 'en';
						break;
				}
				$messageData = array(
					'message' => $message,
					'offer_id' => $row['id'],
				);
				
				$isDevp = ('api' == $this->module->id) ? false : true;
				BUtils::PushNotificationToDevice($v['device_notification_token'], $v['device_type'], $messageData, $isDevp);

				// save to table: tbl_user_device_pushing_log, 2012.12.28, Bocheng
				$sql_pushing_log = 'INSERT INTO tbl_user_device_pushing_log (device_token, device_type, id_special, id_user, distance)
					VALUES(:device_token, :device_type, :id_special, :id_user, :distance)';
				$params_pushing = array(
					':device_token' => $device_token,
					':device_type' => $v['device_type'],
					':id_user' => $v['id_user'],
					':id_special' => $row['id'],
					':distance' => $row['distance'],
				);
				$command = $connection->createCommand($sql_pushing_log);
				$command->execute($params_pushing);
			}
		}
		$connection->active = false;
		
		self::APILogging('actionPushOffer', array(
			'microtime' => microtime(true),
			'token' => $device_token,
			'location' => "[{$v['latitude']}, {$v['longitude']}]",
			'moving_distance' => $moving_distance,
		), 'specials_pushOffer');
		$this->_sendJSON(200, 'OK');
	}
	
	/**
	 * API: specials/notify_GiveOffers
	 * This is old API for giving offers nearby.
	 */
	public function actionNotify_GiveOffers()
	{
		// set the limit number and offset
		$offset = Yii::app()->request->getParam('offset', 0);
		$limit = Yii::app()->request->getParam('limit', 10);
		if ($limit > 10) $limit = 10;
		$s_limit = " LIMIT {$offset}, " . $limit;
		
		$params = array();
		
		// get distance if lat=? and lng=?
		$latitude = Yii::app()->request->getParam('lat');
		$longitude= Yii::app()->request->getParam('lng');
		if (empty($latitude) or empty($longitude)) {
			$this->_sendError('The parameter "lat" or "lng" is missing!');
		}
		$s_distance = '(6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance, ';
		$params[':lat'] = (double)$latitude;
		$params[':lng'] = (double)$longitude;
		
		// get sort:
		$s_orderby = '';
		$this->_setOrderBy($s_orderby);
		
		$s_status = null;
		$s_where_status = ' AND distance<=:distance AND discount>=:discount ';
		// get hid (user id)
		$id = null;
		$hid = Yii::app()->request->getParam('hid');
		$key = Yii::app()->request->getParam('key');
		if (!empty($hid) and !empty($key)) {
			$id = $this->_auth($hid, $key);
		}
		// get device_token: token=?
		$device_token = Yii::app()->request->getParam('token');
		if (!empty($device_token) or !empty($id)) {
			$s_status = ', (SELECT r3.redeemed FROM tbl_merchant_special_redeem r3
				WHERE r3.id_special=s.id AND (r3.device_token=:device_token OR r3.id_user=:id_user) AND DATE(r3.insert_timestamp)=DATE(NOW())
				ORDER BY r3.insert_timestamp DESC LIMIT 1) AS redeemed_status
				, (SELECT COUNT(f.id) FROM tbl_user_follow_merchant f
			       WHERE f.id_user=:id_user AND f.id_merchant=m.id) AS following ';	// user follows the merchant
			$s_where_status = ' AND (redeemed_status IS NULL OR redeemed_status=0) AND (distance<=:distance AND discount>=:discount OR following>0) '; //no claimed/redeemed + distance + discount + following
			$params[':id_user'] = $id;
			$params[':device_token'] = $device_token;
		}
		
		// fetch specials
		// comments: only care today's claimed number: DATE(r1.insert_timestamp)=DATE(NOW())
		$params[':week_days'] = '%'.date('N').'%';	//week_days => 5
		$params[':hour_min'] = (int)date('Gi');		//hour_min => 934
		$sql_specials = 'SELECT s.id, s.id_merchant, s.url_picture, s.content, s.show_hours, s.start_hour, s.end_hour, s.week_days,
			s.price, s.value, s.discount, s.highlight, s.type, ' . $s_distance . '
			UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date, UNIX_TIMESTAMP(s.published_time) AS uts_published_time,
			s.max_number, s.redeem_type,
			m.name, m.address, m.biz_area, m.postcode, m.city, m.phone, m.url_logo, m.url_offer_img, m.currency_code, m.category_key, m.latitude, m.longitude, '
			. ' (SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1
				 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num, '	// calculate today's claimed_num
			. ' (SELECT SUM(r2.number) FROM tbl_merchant_special_redeem r2
				 WHERE r2.id_special=s.id AND r2.redeemed=2 AND DATE(r2.redeemed_time)=DATE(NOW()) ) AS redeemed_num '	// calculate today's redeemed_num
			. $s_status . '
			FROM tbl_merchant_special s '
			. ' INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant '
			. ' WHERE s.status=1 AND s.trash=0 AND m.status<2 AND s.start_date<NOW() AND s.end_date>NOW() '
			. '   AND s.price IS NOT NULL AND s.value IS NOT NULL  '//TODO: work for old versions
			. '   AND (s.week_days LIKE :week_days AND s.start_hour<=:hour_min AND s.end_hour>=:hour_min) '	//for today and valid time checking
			. ' HAVING (claimed_num<s.max_number OR claimed_num IS NULL OR s.max_number=0 OR s.max_number IS NULL) 
				' . $s_where_status
			. ' ORDER BY ' . $s_orderby
			. $s_limit;
		$connection = Yii::app()->db;
		// get user settings
		if (!empty($device_token)) {
			$sql_settings = 'SELECT max_distance, min_discount FROM tbl_user_device_settings WHERE device_token=:device_token ';
			$command = $connection->createCommand($sql_settings);
			$row_settings = $command->queryRow(true, array(':device_token' => $device_token));
			// set the max_distance
			//$params[':distance'] = $row_settings['max_distance'];
			$params[':distance'] = $row_settings['max_distance'] / 10;//transfer the distance
			$params[':discount'] = abs($row_settings['min_discount']);
		}
		
		// get filter: distance=?
		$distance = Yii::app()->request->getParam('distance');
		if (!empty($distance)) {
			//if ($distance < 0.5) $distance = 0.5;	//min distance = 100m
			//elseif ($distance > 1) $distance = 1;	//max distance = 1km
			//transfer the distance
			$distance = $distance / 10;
			if ($distance < 0.05) $distance = 0.05;	//min distance = 50m
			elseif ($distance > 1) $distance = 1;	//max distance = 1000m

			// set the max_distance
			$params[':distance'] = $distance;
		}
		if (empty($params[':distance'])) $params[':distance'] = 0.3;	// default = 300m
		
		// get filter: discount=?
		$discount = Yii::app()->request->getParam('discount');
		if (!empty($discount)) {
			$discount = abs($discount);
			if ($discount < 0.2) $discount = 0.2;	//min discount = 20%
			elseif ($discount > 0.5) $distance = 0.5;	//max discount = 50%
			// set the max_distance
			$params[':discount'] = $discount;
		}
		if (empty($params[':discount'])) $params[':discount'] = 0.2;	// default = 20%
		
		$command = $connection->createCommand($sql_specials);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		$this->_sendJSON(200, $this->_mkSpecials($rows), array(
			'new_num' => 1,
			'interval' => 300,
		));
	}
	
	/**
	 * This action is for report offers which are notified to a device.
	 */
	public function actionNotified()
	{
		//get device
		$device = Yii::app()->request->getParam('device', null);
		$token = Yii::app()->request->getParam('token', null);
		
		//get offer ids
		$ids = Yii::app()->request->getParam('id');
		if (empty($ids)) $this->_sendError('The parameter "id" is missing!');
		$ids_array = explode(',', $ids);
		$ids_count = count($ids_array);
		if ($ids_count < 1) {
			$this->_sendError('The parameter "id" is empty!');
		}
		
		//get distance
		$distance = Yii::app()->request->getParam('distance');
		$distance_array = explode(',', $distance);
		
		$params = array(
			':id_special' => null,
			':device_type' => $device,
			':device_token' => $token,
			':id_user' => null,
			':distance' => null,
			':agent_string' => Yii::app()->request->getUserAgent(),
		);
		
		//get user id
		$hid = Yii::app()->request->getParam('hid');
		$key = Yii::app()->request->getParam('key');
		if (!empty($hid) and !empty($key)) {
			$id = $this->_auth($hid, $key);
			$params[':id_user'] = $id;
		}
		
		$connection = Yii::app()->db;
		$sql_push_logging = 'INSERT INTO tbl_user_device_pushing_log (device_token, device_type, id_special, id_user, distance, agent_string) 
			VALUES (:device_token, :device_type, :id_special, :id_user, :distance, :agent_string)';
		$command = $connection->createCommand($sql_push_logging);
		for ($i = 0; $i < $ids_count; $i ++) {
			$params[':id_special'] = $ids_array[$i];
			$params[':distance'] = $distance_array[$i];
			$command->execute($params);
		}
		$connection->active = false;
		$this->_sendJSON(200, 'success');
	}
	
	/**
	 * API: specials/claimed
	 */
	public function actionClaimed()
	{
		$params = array();
		
		// get hid (user id)
		$id = null;
		$hid = Yii::app()->request->getParam('hid');
		$key = Yii::app()->request->getParam('key');
		if (!empty($hid) and !empty($key)) {
			$id = $this->_auth($hid, $key);
		}
		// get device_token: token=?
		$device_token = Yii::app()->request->getParam('token');
		if (empty($device_token) and empty($id)) {
			$this->_sendJSON(300, BUtils::T('no_login_reserve_special_msg'));
		}
		
		$s_status = ', (SELECT r3.redeemed FROM tbl_merchant_special_redeem r3
				WHERE r3.id_special=s.id AND (r3.device_token=:device_token OR r3.id_user=:id_user) AND DATE(r3.insert_timestamp)=DATE(NOW())
				ORDER BY r3.insert_timestamp DESC LIMIT 1) AS redeemed_status
				, (SELECT unix_timestamp(r4.redeemed_time) FROM tbl_merchant_special_redeem r4
				 WHERE r4.id_special=s.id AND (r4.device_token=:device_token OR r4.id_user=:id_user) AND DATE(r4.insert_timestamp)=DATE(NOW())
				 ORDER BY r4.insert_timestamp DESC LIMIT 1) AS redeemed_time
				, (SELECT COUNT(f.id) FROM tbl_user_follow_merchant f
				   WHERE f.id_user=:id_user AND f.id_merchant=m.id) AS following
				, (SELECT unix_timestamp(r5.claimed_time) FROM tbl_merchant_special_redeem r5
				 WHERE r5.id_special=s.id AND (r5.device_token=:device_token OR r5.id_user=:id_user) AND DATE(r5.insert_timestamp)=DATE(NOW())
				 ORDER BY r5.insert_timestamp DESC LIMIT 1) AS claimed_time';
		$params[':id_user'] = $id;
		$params[':device_token'] = $device_token;
		
		// set the limit number and offset
		$offset = Yii::app()->request->getParam('offset', 0);
		$limit = Yii::app()->request->getParam('limit', 50);
		if ($limit > 50) $limit = 50;
		$s_limit = " LIMIT {$offset}, " . $limit;
		
		// get distance if lat=? and lng=?
		$s_distance = 'NULL AS distance, ';
		$latitude = Yii::app()->request->getParam('lat');
		$longitude= Yii::app()->request->getParam('lng');
		if (!empty($latitude) and !empty($longitude)) {
			$s_distance = '(6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance, ';
			$params[':lat'] = (double)$latitude;
			$params[':lng'] = (double)$longitude;
		}
		
		// get sort:
		$s_orderby = '';
		$this->_setOrderBy($s_orderby);
		
		// fetch specials
		// comments: only care today's claimed number: DATE(r1.insert_timestamp)=DATE(NOW())
		$sql_specials = 'SELECT s.id, s.id_merchant, s.url_picture, s.content, s.show_hours, s.start_hour, s.end_hour, s.week_days,
			s.price, s.value, s.discount, s.highlight, s.type, ' . $s_distance . '
			UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date, UNIX_TIMESTAMP(s.published_time) AS uts_published_time,
			s.max_number, s.redeem_type,
			m.name, m.address, m.biz_area, m.postcode, m.city, m.phone, m.url_logo, m.url_offer_img, m.currency_code, m.category_key, m.latitude, m.longitude, '
			. ' (SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1
				 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num, '	// calculate today's claimed_num
			. ' (SELECT SUM(r2.number) FROM tbl_merchant_special_redeem r2
				 WHERE r2.id_special=s.id AND r2.redeemed=2 AND DATE(r2.redeemed_time)=DATE(NOW()) ) AS redeemed_num '	// calculate today's redeemed_num
			. $s_status . ' 
			FROM tbl_merchant_special s '
			. ' INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant '
			. ' WHERE s.status=1 AND s.trash=0 '
			. ' HAVING redeemed_status IS NOT NULL '
			. ' ORDER BY ' . $s_orderby
			. $s_limit;
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_specials);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		$this->_sendJSON(200, $this->_mkSpecials($rows));
	}
	
	/**
	 * API: specials/redeemed
	 */
	public function actionRedeemed()
	{
		$params = array();
		
		// get hid (user id)
		$id = null;
		$hid = Yii::app()->request->getParam('hid');
		$key = Yii::app()->request->getParam('key');
		if (!empty($hid) and !empty($key)) {
			$id = $this->_auth($hid, $key);
		}
		// get device_token: token=?
		$device_token = Yii::app()->request->getParam('token');
		if (empty($device_token) and empty($id)) {
			$this->_sendError('The parameter "token" or "hid" is missing!');
		}
		
		$s_status = ', r.redeemed AS redeemed_status
				, unix_timestamp(r.redeemed_time) AS redeemed_time
				, (SELECT COUNT(f.id) FROM tbl_user_follow_merchant f
				   WHERE f.id_user=:id_user AND f.id_merchant=m.id) AS following ';
		$params[':id_user'] = $id;
		$params[':device_token'] = $device_token;
		
		// set the limit number and offset
		$offset = Yii::app()->request->getParam('offset', 0);
		$limit = Yii::app()->request->getParam('limit', 50);
		if ($limit > 50) $limit = 50;
		$s_limit = " LIMIT {$offset}, " . $limit;
		
		// get distance if lat=? and lng=?
		$s_distance = 'NULL AS distance, ';
		$latitude = Yii::app()->request->getParam('lat');
		$longitude= Yii::app()->request->getParam('lng');
		if (!empty($latitude) and !empty($longitude)) {
			$s_distance = '(6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance, ';
			$params[':lat'] = (double)$latitude;
			$params[':lng'] = (double)$longitude;
		}
		
		// get sort:
		$s_orderby = '';
		$this->_setOrderBy($s_orderby);
		
		// fetch specials
		// comments: only care today's claimed number: DATE(r1.insert_timestamp)=DATE(NOW())
		$sql_specials = 'SELECT s.id, s.id_merchant, s.url_picture, s.content, s.show_hours, s.start_hour, s.end_hour, s.week_days,
			s.price, s.value, s.discount, s.highlight, s.type, ' . $s_distance . '
			UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date, UNIX_TIMESTAMP(s.published_time) AS uts_published_time,
			s.max_number, s.redeem_type, r.redeemed_time,
			m.name, m.address, m.biz_area, m.postcode, m.city, m.phone, m.url_logo, m.url_offer_img, m.currency_code, m.category_key, m.latitude, m.longitude, '
			. ' (SELECT SUM(r1.number) FROM tbl_merchant_special_redeem r1
				 WHERE r1.id_special=s.id AND DATE(r1.insert_timestamp)=DATE(NOW()) ) AS claimed_num, '	// calculate today's claimed_num
			. ' (SELECT SUM(r2.number) FROM tbl_merchant_special_redeem r2
				 WHERE r2.id_special=s.id AND r2.redeemed=2 AND DATE(r2.redeemed_time)=DATE(NOW()) ) AS redeemed_num '	// calculate today's redeemed_num
			. $s_status . ' 
			FROM tbl_merchant_special_redeem r '
			. ' INNER JOIN tbl_merchant_special s ON s.id=r.id_special '
			. ' INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant '
			. ' WHERE (r.device_token=:device_token OR r.id_user=:id_user) AND r.redeemed=2 AND DATE(r.redeemed_time)=DATE(NOW()) '
			. ' GROUP BY r.id '
			. ' ORDER BY ' . $s_orderby
			. $s_limit;
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_specials);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		$this->_sendJSON(200, $this->_mkSpecials($rows));
	}
	
	/**
	 * 
	 * @param array $rows
	 * @return array
	 */
	protected function _mkSpecials($rows) {
		$norm_special = BUtils::T('special_lbl');
		
		$week_array = BUtils::WeekDayKeys(7, true);
		$categoriesIndex = BUtils::CategoriesNo();
		
		$specials = array();
		foreach ($rows as $r) {
			//set the day string
			$week_string = self::GetWeekdays($r['week_days'], $week_array);
			$range_date = self::GetRecentDayName($r['week_days'], $week_string, ($this->action->id == 'next24'), $r['start_hour']);
			$period_string = BUtils::GetUntilDate($r['uts_end_date']);
			
			//set the valid_time
			$range_time = BUtils::GetHourRangeFormat($r['start_hour'], $r['end_hour'], $r['show_hours']);
			
			//logo
			$url_logo = null;
			if (!empty($r['url_logo'])) {
				$url_logo = 'http://yamii.fi/business' . $r['url_logo'];
			}
			
			//set offer img
			$url_thumb = 'http://yamii.fi/img/default_special_thumb.png';
			$url_picture = 'http://yamii.fi/img/default_special_picture.png';
			if (!empty($r['url_picture'])) {
				$url_thumb = 'http://yamii.fi/business' . $r['url_picture'];
				$url_picture = 'http://yamii.fi/business' . str_replace('thumb_', '', $r['url_picture']);
			} elseif (!empty($r['url_offer_img'])) {
				$url_thumb = 'http://yamii.fi/business' . $r['url_offer_img'];
				$url_picture = 'http://yamii.fi/business' . str_replace('thumb_', '', $r['url_offer_img']);
			}
			
			//following
			if (empty($r['following']) or $r['following'] == 0) {
				$following = 0;
			} else {
				$following = 1;
			}
			
			//set price and discount
			if (empty($r['discount'])) {
				$r['discount'] = null;
			} else {
				$r['discount'] = round($r['discount'], 2);
			}
			if (empty($r['price'])) $price = null;
			else $price = (float)$r['price'];
			if (empty($r['value'])) $value = null;
			else $value = (float)$r['value'];
			if (empty($r['discount'])) $discount = null;
			else $discount = (float)$r['discount'];
			$highlight = $r['highlight'];
			
			//set category
			if (empty($r['category_key'])) {
				$category = '';
			} else $category = BUtils::T($r['category_key']);
			$cate_no = (int)$categoriesIndex[$r['category_key']];
			
			//set new flag
			$new_flag = $hot_flag = 0;
			if (time() - $r['uts_start_date'] < 12*3600) {
				$new_flag = 1;
			}
			
			//set redeem type
			$redeem_type = (int)$r['redeem_type'];
			$specials[] = array(
				'id' => $r['id'],
				'url_thumb' => $url_thumb,
				'url_picture' => $url_picture,
				'new_flag' => $new_flag,//new
				'hot_flag' => $hot_flag,//new
				'title' => $norm_special,
				'content' => $r['content'],
				'price' => $price,
				'value' => $value,
				'discount' => $discount,
				'highlight' => $highlight,//new
				'currency' => $r['currency_code'],
				'range_time' => $range_time,
				'range_date' => $range_date,
				'week' => $week_string,
				'period' => $period_string,//new
				'start_time' => (int)$r['uts_start_date'],
				'end_time' => (int)$r['uts_end_date'],
				'published_time' => (int)$r['uts_published_time'],
				'max_num' => (int)$r['max_number'],
				'redeem_type' => $redeem_type,//new
				'claimed_num' => (int)$r['claimed_num'],
				'redeemed_num' => (int)$r['redeemed_num'],
				'status' => empty($r['redeemed_status']) ? 0 : (int)$r['redeemed_status'],
				'redeemed_time' => empty($r['redeemed_time']) ? 0 : (int)$r['redeemed_time'],
				'merchant' => array(
					'id' => $r['id_merchant'],
					'name' => $r['name'],
					'category' => $category,
					'category_no' => $cate_no,//new
					'url_logo' => $url_logo,
					'area' => empty($r['biz_area']) ? '' : $r['biz_area'],
					'address' => empty($r['address']) ? '' : $r['address'],
					'zip' => $r['postcode'],
					'city' => $r['city'],
					'phone' => MerchantObject::formatPhoneNumber($r['phone']),
					'distance' => empty($r['distance']) ? null : (float)$r['distance'],
					'lat' => (float)$r['latitude'],
					'lng' => (float)$r['longitude'],
					'following' => $following,
				),
			);
		}
		return $specials;
	}
	
	protected static function _setFilters(&$s_where, &$params) {
		$city_key = Yii::app()->request->getParam('city', 'capital_cities');
		if ($city_key == 'capital_cities') {
			$s_where = ' AND m.city IN (:city, :city2, :city3) ';
			$params[':city'] = "helsinki";
			$params[':city2'] = "espoo";
			$params[':city3'] = "vantaa";
		} else {
			$s_where = ' AND m.city LIKE :city ';
			$params[':city'] = "%{$city_key}%";
		}
		$query_key = Yii::app()->request->getParam('q');
		if (!empty($query_key)) {
			$s_where .= ' AND (s.content LIKE :q1 OR m.name LIKE :q1 OR m.address LIKE :q1 OR m.biz_area LIKE :q1 OR m.postcode=:q2) ';
			$params[':q1'] = "%{$query_key}%";
			$params[':q2'] = $query_key;
		}
		
		// get business area filter: area=?
		$area_string = Yii::app()->request->getParam('area');
		if (!empty($area_string) and $area_string != 'all') {
			$s_where .= ' AND m.biz_area LIKE :biz_area ';
			$params[':biz_area'] = "%{$area_string}%";
		}
		// get category_no=?
		$category_no = Yii::app()->request->getParam('category_no');
		if (!empty($category_no)) {
			$category_array = BUtils::CategoriesNo();
			$key = array_search($category_no, $category_array);
			if (false !== $key) {
				$s_where .= ' AND m.category_key=:category_key ';
				$params[':category_key'] = $key;
			}
		}
	}
	
	protected static function _setOrderBy(&$s_orderby) {
		// get sort: sort=newest/nearby
		$sort_key = Yii::app()->request->getParam('order_by', Yii::app()->request->getParam('sort'));
		switch (strtolower($sort_key)) {
			case 'newest':
				$s_orderby = ' s.published_time DESC ';
				break;
			default:
				$s_orderby = ' distance ASC, s.published_time DESC ';
				break;
		}
	}
}